Overview
This node allows executing SQL queries with bind variables on an Oracle Database. It supports parameterized queries, including input, output, and input-output parameters, enabling complex database operations such as data retrieval, insertion, update, and stored procedure calls.
Common scenarios include:
- Running SELECT queries with dynamic filters.
- Executing INSERT/UPDATE statements with parameters.
- Calling stored procedures that use REF CURSORs or output parameters.
- Handling batch operations by passing lists of values for IN clauses.
For example, you can query products with IDs less than a certain value by providing a parameter param_name and its value, or call a stored procedure that returns a cursor as an output parameter.
Properties
| Name | Meaning |
|---|---|
| SQL Statement | The SQL query to execute. Supports bind variables using colon-prefixed names (e.g., :param_name). |
| Parameters | A collection of parameters to bind to the SQL statement. Each parameter includes: - Name: The bind variable name without the colon prefix. - Data Type: One of String, Number, Date, or REF CURSOR (OUT). - Direction: IN, OUT, or IN OUT. - Value: The value for IN or IN OUT parameters. - Parse for IN statement: If "Yes", treats the value as a comma-separated list for expanding into multiple bind variables (only valid for IN direction and non-cursor types). - Max Size: Required for OUT or IN OUT string parameters to specify maximum size. |
Output
The node outputs a JSON array containing one object with the following fields:
metaData: Metadata about the returned columns (if any).rows: An array of result rows as objects (for SELECT or queries returning data).rowsAffected: Number of rows affected by the query (for INSERT, UPDATE, DELETE).lastRowid: The last inserted row ID if applicable.outBinds: An object containing output bind parameters and their values, including any REF CURSOR results expanded into arrays of rows.
If the query involves REF CURSOR output parameters, these are automatically fetched and included in the outBinds.
The node does not output binary data.
Dependencies
- Requires an Oracle Database connection configured via credentials that provide user, password, and connection string.
- Uses the official OracleDB Node.js driver (
oracledbpackage). - Requires n8n credentials setup for Oracle Database authentication.
- No additional environment variables are explicitly required beyond standard Oracle DB connection info.
Troubleshooting
- Parameter Direction Errors: Using "Parse for IN statement" with OUT or IN OUT parameters will cause errors. Ensure this option is only used with IN parameters.
- CURSOR Parameter Restrictions: REF CURSOR type cannot be used with IN or IN OUT directions; it must be OUT only.
- Empty IN List: If "Parse for IN statement" is enabled but the value is empty or contains no valid items, an error is thrown.
- Max Size Missing: For OUT or IN OUT string parameters, specifying Max Size is mandatory; omitting it may cause runtime errors.
- Connection Issues: Failure to connect to the Oracle DB will throw errors; verify credentials and network access.
- Query Syntax: Invalid SQL syntax or incorrect bind variable names will cause execution errors.
- Resource Cleanup: The node attempts to close the DB connection after execution; failures here are logged but do not stop the workflow.